import { McpServer, ResourceTemplate } from '@modelcontextprotocol/sdk/server/mcp.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import { z } from 'zod';
import { db } from '@/lib/db';
import { orders, orderItems, customers, products, orderActivities, inventory } from '@/lib/schema';
import { eq, desc, sql, and, like, or, gte, lte } from 'drizzle-orm';

// Create MCP server
export const server = new McpServer({
  name: 'seller-management-api',
  version: '1.0.0',
  description: 'MCP server for seller management system API'
});

// Helper function to get order details
async function getOrderDetails(orderId: number) {
  const order = await db
    .select({
      id: orders.id,
      customerId: orders.customerId,
      customerName: customers.name,
      customerEmail: customers.email,
      customerPhone: customers.phone,
      customerAddress: customers.address,
      totalAmount: orders.totalAmount,
      status: orders.status,
      cancelReason: orders.cancelReason,
      createdAt: orders.createdAt,
      updatedAt: orders.updatedAt
    })
    .from(orders)
    .leftJoin(customers, eq(orders.customerId, customers.id))
    .where(eq(orders.id, orderId))
    .limit(1);

  if (order.length === 0) {
    return null;
  }

  const items = await db
    .select({
      id: orderItems.id,
      productId: orderItems.productId,
      productName: products.name,
      productPrice: orderItems.price,
      quantity: orderItems.quantity,
      subtotal: orderItems.subtotal
    })
    .from(orderItems)
    .leftJoin(products, eq(orderItems.productId, products.id))
    .where(eq(orderItems.orderId, orderId));

  const activities = await db
    .select({
      id: orderActivities.id,
      action: orderActivities.action,
      fromStatus: orderActivities.fromStatus,
      toStatus: orderActivities.toStatus,
      description: orderActivities.description,
      performedBy: orderActivities.performedBy,
      createdAt: orderActivities.createdAt
    })
    .from(orderActivities)
    .where(eq(orderActivities.orderId, orderId))
    .orderBy(desc(orderActivities.createdAt));

  return {
    ...order[0],
    items,
    activities
  };
}

// Helper function to get status text
function getStatusText(status: string): string {
  const statusMap: Record<string, string> = {
    'pending': '待支付',
    'paid': '已支付',
    'shipped': '已发货',
    'delivered': '已交付',
    'cancelled': '已取消'
  };
  return statusMap[status] || status;
}

// Order Management Tools

server.registerTool(
  'get-order',
  {
    title: '获取订单详情',
    description: '根据订单ID获取订单详细信息，包括商品明细和活动记录',
    inputSchema: {
      orderId: z.number().describe('订单ID')
    }
  },
  async ({ orderId }) => {
    try {
      const order = await getOrderDetails(orderId);
      if (!order) {
        return {
          content: [{ type: 'text', text: `订单 ${orderId} 不存在` }],
          isError: true
        };
      }

      const orderText = `订单 #${order.id}
客户: ${order.customerName} (${order.customerEmail})
状态: ${getStatusText(order.status)}
总金额: ¥${order.totalAmount}
创建时间: ${new Date(order.createdAt * 1000).toLocaleString('zh-CN')}
更新时间: ${new Date(order.updatedAt * 1000).toLocaleString('zh-CN')}`;

      const itemsText = order.items.map(item => 
        `- ${item.productName}: ${item.quantity} × ¥${item.price} = ¥${item.subtotal}`
      ).join('\n');

      const activitiesText = order.activities.map(activity => 
        `[${new Date(activity.createdAt * 1000).toLocaleString('zh-CN')}] ${activity.description}`
      ).join('\n');

      const result = `${orderText}\n\n商品明细:\n${itemsText}\n\n活动记录:\n${activitiesText}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `获取订单详情失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

server.registerTool(
  'list-orders',
  {
    title: '订单列表查询',
    description: '根据条件查询订单列表，支持状态、日期范围、客户名称等筛选',
    inputSchema: {
      status: z.string().optional().describe('订单状态: pending, paid, shipped, delivered, cancelled'),
      startDate: z.string().optional().describe('开始日期 (YYYY-MM-DD)'),
      endDate: z.string().optional().describe('结束日期 (YYYY-MM-DD)'),
      customerName: z.string().optional().describe('客户名称'),
      customerEmail: z.string().optional().describe('客户邮箱'),
      minAmount: z.number().optional().describe('最小金额'),
      maxAmount: z.number().optional().describe('最大金额'),
      limit: z.number().optional().default(20).describe('返回数量限制'),
      offset: z.number().optional().default(0).describe('偏移量')
    }
  },
  async ({ status, startDate, endDate, customerName, customerEmail, minAmount, maxAmount, limit, offset }) => {
    try {
      let query = db
        .select({
          id: orders.id,
          customerName: customers.name,
          customerEmail: customers.email,
          totalAmount: orders.totalAmount,
          status: orders.status,
          createdAt: orders.createdAt,
          updatedAt: orders.updatedAt
        })
        .from(orders)
        .leftJoin(customers, eq(orders.customerId, customers.id));

      // Apply filters
      const conditions = [];
      
      if (status) {
        conditions.push(eq(orders.status, status));
      }
      
      if (startDate) {
        const startTimestamp = Math.floor(new Date(startDate).getTime() / 1000);
        conditions.push(gte(orders.createdAt, startTimestamp));
      }
      
      if (endDate) {
        const endTimestamp = Math.floor(new Date(endDate).getTime() / 1000) + 86400; // Add 1 day
        conditions.push(lte(orders.createdAt, endTimestamp));
      }
      
      if (customerName) {
        conditions.push(like(customers.name, `%${customerName}%`));
      }
      
      if (customerEmail) {
        conditions.push(like(customers.email, `%${customerEmail}%`));
      }
      
      if (minAmount !== undefined) {
        conditions.push(gte(orders.totalAmount, minAmount));
      }
      
      if (maxAmount !== undefined) {
        conditions.push(lte(orders.totalAmount, maxAmount));
      }

      if (conditions.length > 0) {
        query = query.where(and(...conditions));
      }

      const orderList = await query
        .orderBy(desc(orders.createdAt))
        .limit(limit || 20)
        .offset(offset || 0);

      const totalCount = await db
        .select({ count: sql<number>`count(*)` })
        .from(orders)
        .leftJoin(customers, eq(orders.customerId, customers.id))
        .where(conditions.length > 0 ? and(...conditions) : undefined);

      const ordersText = orderList.map(order => 
        `#${order.id} | ${getStatusText(order.status)} | ¥${order.totalAmount} | ${order.customerName} | ${new Date(order.createdAt * 1000).toLocaleDateString('zh-CN')}`
      ).join('\n');

      const result = `找到 ${totalCount[0]?.count || 0} 个订单\n\n${ordersText || '未找到符合条件的订单'}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `查询订单列表失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

server.registerTool(
  'update-order-status',
  {
    title: '更新订单状态',
    description: '更新订单状态并处理相关库存操作',
    inputSchema: {
      orderId: z.number().describe('订单ID'),
      status: z.enum(['pending', 'paid', 'shipped', 'delivered', 'cancelled']).describe('新状态'),
      cancelReason: z.string().optional().describe('取消原因（仅当状态为cancelled时有效）')
    }
  },
  async ({ orderId, status, cancelReason }) => {
    try {
      // Get current order status
      const [currentOrder] = await db
        .select({ status: orders.status })
        .from(orders)
        .where(eq(orders.id, orderId));

      if (!currentOrder) {
        return {
          content: [{ type: 'text', text: `订单 ${orderId} 不存在` }],
          isError: true
        };
      }

      // Validate status transition
      const validTransitions: Record<string, string[]> = {
        pending: ['paid', 'cancelled'],
        paid: ['shipped', 'cancelled'],
        shipped: ['delivered'],
        delivered: [],
        cancelled: []
      };

      if (!validTransitions[currentOrder.status]?.includes(status)) {
        return {
          content: [{ type: 'text', text: `无法从${getStatusText(currentOrder.status)}变更为${getStatusText(status)}` }],
          isError: true
        };
      }

      // Handle inventory changes
      if (status === 'cancelled' && currentOrder.status === 'paid') {
        const items = await db
          .select({
            productId: orderItems.productId,
            quantity: orderItems.quantity
          })
          .from(orderItems)
          .where(eq(orderItems.orderId, orderId));

        for (const item of items) {
          const [currentInventory] = await db
            .select({ quantity: inventory.quantity })
            .from(inventory)
            .where(eq(inventory.productId, item.productId));

          if (currentInventory) {
            await db
              .update(inventory)
              .set({ quantity: currentInventory.quantity + item.quantity })
              .where(eq(inventory.productId, item.productId));
          }
        }
      }

      // Update order
      const updateData: any = {
        status,
        updatedAt: sql`(strftime('%s', 'now'))`
      };

      if (status === 'cancelled' && cancelReason) {
        updateData.cancelReason = cancelReason;
      }

      const [updatedOrder] = await db
        .update(orders)
        .set(updateData)
        .where(eq(orders.id, orderId))
        .returning();

      // Record activity
      let description = `订单状态从 ${getStatusText(currentOrder.status)} 变更为 ${getStatusText(status)}`;
      if (status === 'cancelled' && cancelReason) {
        description += `，原因：${cancelReason}`;
      }

      await db.insert(orderActivities).values({
        orderId,
        action: 'status_change',
        fromStatus: currentOrder.status,
        toStatus: status,
        description,
        performedBy: 'mcp-tool'
      });

      return {
        content: [{ type: 'text', text: `订单 ${orderId} 状态已更新为 ${getStatusText(status)}` }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `更新订单状态失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

// Customer Management Tools

server.registerTool(
  'get-customer',
  {
    title: '获取客户信息',
    description: '根据客户ID获取客户详细信息',
    inputSchema: {
      customerId: z.number().describe('客户ID')
    }
  },
  async ({ customerId }) => {
    try {
      const customer = await db
        .select()
        .from(customers)
        .where(eq(customers.id, customerId))
        .limit(1);

      if (customer.length === 0) {
        return {
          content: [{ type: 'text', text: `客户 ${customerId} 不存在` }],
          isError: true
        };
      }

      const customerData = customer[0];
      const result = `客户 #${customerData.id}
姓名: ${customerData.name}
邮箱: ${customerData.email || '无'}
电话: ${customerData.phone || '无'}
地址: ${customerData.address || '无'}
创建时间: ${new Date(customerData.createdAt * 1000).toLocaleString('zh-CN')}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `获取客户信息失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

server.registerTool(
  'search-customers',
  {
    title: '搜索客户',
    description: '根据名称、邮箱或电话搜索客户',
    inputSchema: {
      searchTerm: z.string().describe('搜索关键词（名称、邮箱或电话）'),
      limit: z.number().optional().default(10).describe('返回数量限制')
    }
  },
  async ({ searchTerm, limit }) => {
    try {
      const customerList = await db
        .select()
        .from(customers)
        .where(or(
          like(customers.name, `%${searchTerm}%`),
          like(customers.email, `%${searchTerm}%`),
          like(customers.phone, `%${searchTerm}%`)
        ))
        .limit(limit || 10)
        .orderBy(desc(customers.createdAt));

      if (customerList.length === 0) {
        return {
          content: [{ type: 'text', text: `未找到匹配 "${searchTerm}" 的客户` }]
        };
      }

      const customersText = customerList.map(customer => 
        `#${customer.id} | ${customer.name} | ${customer.email || '无邮箱'} | ${customer.phone || '无电话'}`
      ).join('\n');

      const result = `找到 ${customerList.length} 个客户:\n\n${customersText}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `搜索客户失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

// Product Management Tools

server.registerTool(
  'get-product',
  {
    title: '获取产品信息',
    description: '根据产品ID获取产品详细信息',
    inputSchema: {
      productId: z.number().describe('产品ID')
    }
  },
  async ({ productId }) => {
    try {
      const product = await db
        .select({
          id: products.id,
          name: products.name,
          description: products.description,
          price: products.price,
          category: products.category,
          sku: products.sku,
          createdAt: products.createdAt
        })
        .from(products)
        .where(eq(products.id, productId))
        .limit(1);

      if (product.length === 0) {
        return {
          content: [{ type: 'text', text: `产品 ${productId} 不存在` }],
          isError: true
        };
      }

      const productData = product[0];
      
      // Get inventory info
      const [inventoryInfo] = await db
        .select({
          quantity: inventory.quantity,
          lowStockThreshold: inventory.lowStockThreshold
        })
        .from(inventory)
        .where(eq(inventory.productId, productId));

      const result = `产品 #${productData.id}
名称: ${productData.name}
描述: ${productData.description || '无'}
价格: ¥${productData.price}
分类: ${productData.category || '未分类'}
SKU: ${productData.sku || '无'}
库存: ${inventoryInfo?.quantity || 0} 件
低库存阈值: ${inventoryInfo?.lowStockThreshold || 10} 件
创建时间: ${new Date(productData.createdAt * 1000).toLocaleString('zh-CN')}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `获取产品信息失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

server.registerTool(
  'list-products',
  {
    title: '产品列表查询',
    description: '查询产品列表，支持按分类、价格范围等筛选',
    inputSchema: {
      category: z.string().optional().describe('产品分类'),
      minPrice: z.number().optional().describe('最低价格'),
      maxPrice: z.number().optional().describe('最高价格'),
      inStock: z.boolean().optional().describe('仅显示有库存的产品'),
      limit: z.number().optional().default(20).describe('返回数量限制'),
      offset: z.number().optional().default(0).describe('偏移量')
    }
  },
  async ({ category, minPrice, maxPrice, inStock, limit, offset }) => {
    try {
      let query = db
        .select({
          id: products.id,
          name: products.name,
          price: products.price,
          category: products.category,
          sku: products.sku
        })
        .from(products);

      const conditions = [];
      
      if (category) {
        conditions.push(eq(products.category, category));
      }
      
      if (minPrice !== undefined) {
        conditions.push(gte(products.price, minPrice));
      }
      
      if (maxPrice !== undefined) {
        conditions.push(lte(products.price, maxPrice));
      }

      if (inStock) {
        query = query.leftJoin(inventory, eq(products.id, inventory.productId));
        conditions.push(gte(inventory.quantity, 1));
      }

      if (conditions.length > 0) {
        query = query.where(and(...conditions));
      }

      const productList = await query
        .orderBy(desc(products.createdAt))
        .limit(limit || 20)
        .offset(offset || 0);

      if (productList.length === 0) {
        return {
          content: [{ type: 'text', text: '未找到符合条件的商品' }]
        };
      }

      const productsText = productList.map(product => 
        `#${product.id} | ${product.name} | ¥${product.price} | ${product.category || '未分类'} | ${product.sku || '无SKU'}`
      ).join('\n');

      const result = `找到 ${productList.length} 个商品:\n\n${productsText}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `查询产品列表失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

// Inventory Management Tools

server.registerTool(
  'check-inventory',
  {
    title: '检查库存',
    description: '检查产品库存状态，包括低库存预警',
    inputSchema: {
      productId: z.number().describe('产品ID')
    }
  },
  async ({ productId }) => {
    try {
      const [inventoryInfo] = await db
        .select({
          quantity: inventory.quantity,
          lowStockThreshold: inventory.lowStockThreshold
        })
        .from(inventory)
        .where(eq(inventory.productId, productId));

      if (!inventoryInfo) {
        return {
          content: [{ type: 'text', text: `产品 ${productId} 的库存记录不存在` }]
        };
      }

      const product = await db
        .select({ name: products.name })
        .from(products)
        .where(eq(products.id, productId))
        .limit(1);

      const productName = product.length > 0 ? product[0].name : '未知产品';
      
      let status = '正常';
      if (inventoryInfo.quantity === 0) {
        status = '缺货';
      } else if (inventoryInfo.quantity <= inventoryInfo.lowStockThreshold) {
        status = '低库存';
      }

      const result = `产品: ${productName} (#${productId})
当前库存: ${inventoryInfo.quantity} 件
低库存阈值: ${inventoryInfo.lowStockThreshold} 件
状态: ${status}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `检查库存失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

server.registerTool(
  'list-low-stock',
  {
    title: '低库存产品列表',
    description: '列出所有低库存或缺货的产品',
    inputSchema: {
      limit: z.number().optional().default(20).describe('返回数量限制')
    }
  },
  async ({ limit }) => {
    try {
      const lowStockProducts = await db
        .select({
          productId: inventory.productId,
          productName: products.name,
          quantity: inventory.quantity,
          lowStockThreshold: inventory.lowStockThreshold,
          status: sql<string>`CASE 
            WHEN ${inventory.quantity} = 0 THEN '缺货'
            WHEN ${inventory.quantity} <= ${inventory.lowStockThreshold} THEN '低库存'
            ELSE '正常'
          END`
        })
        .from(inventory)
        .leftJoin(products, eq(inventory.productId, products.id))
        .where(or(
          eq(inventory.quantity, 0),
          lte(inventory.quantity, inventory.lowStockThreshold)
        ))
        .orderBy(inventory.quantity)
        .limit(limit || 20);

      if (lowStockProducts.length === 0) {
        return {
          content: [{ type: 'text', text: '没有低库存或缺货的产品' }]
        };
      }

      const productsText = lowStockProducts.map(product => 
        `#${product.productId} | ${product.productName} | ${product.quantity} 件 (阈值: ${product.lowStockThreshold}) | ${product.status}`
      ).join('\n');

      const result = `低库存或缺货产品 (共 ${lowStockProducts.length} 个):\n\n${productsText}`;

      return {
        content: [{ type: 'text', text: result }]
      };
    } catch (error: any) {
      return {
        content: [{ type: 'text', text: `获取低库存列表失败: ${error.message}` }],
        isError: true
      };
    }
  }
);

// Dashboard Resources

server.registerResource(
  'dashboard-stats',
  'dashboard://stats',
  {
    title: '仪表盘统计数据',
    description: '系统仪表盘的关键统计数据',
    mimeType: 'application/json'
  },
  async (uri) => {
    try {
      const [
        totalOrders,
        totalRevenue,
        totalCustomers,
        totalProducts,
        pendingOrders,
        lowStockCount
      ] = await Promise.all([
        db.select({ count: sql<number>`count(*)` }).from(orders),
        db.select({ total: sql<number>`sum(total_amount)` }).from(orders).where(eq(orders.status, 'delivered')),
        db.select({ count: sql<number>`count(*)` }).from(customers),
        db.select({ count: sql<number>`count(*)` }).from(products),
        db.select({ count: sql<number>`count(*)` }).from(orders).where(eq(orders.status, 'pending')),
        db.select({ count: sql<number>`count(*)` }).from(inventory).where(
          or(
            eq(inventory.quantity, 0),
            lte(inventory.quantity, inventory.lowStockThreshold)
          )
        )
      ]);

      const stats = {
        totalOrders: totalOrders[0]?.count || 0,
        totalRevenue: totalRevenue[0]?.total || 0,
        totalCustomers: totalCustomers[0]?.count || 0,
        totalProducts: totalProducts[0]?.count || 0,
        pendingOrders: pendingOrders[0]?.count || 0,
        lowStockProducts: lowStockCount[0]?.count || 0
      };

      return {
        contents: [{
          uri: uri.href,
          text: JSON.stringify(stats, null, 2)
        }]
      };
    } catch (error: any) {
      return {
        contents: [{
          uri: uri.href,
          text: JSON.stringify({ error: `获取统计数据失败: ${error.message}` }, null, 2)
        }]
      };
    }
  }
);

// Export the server for use in different transports
export async function startMcpServer() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.log('MCP Server for Seller Management API is running...');
}

// For standalone execution
if (import.meta.url === `file://${process.argv[1]}`) {
  startMcpServer().catch(console.error);
}